package top.abilities.util.utils.Excel;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Auhtor: ciro
 * @Description
 * @Date: 2018/1/5 17:31
 * @Modified By:
 */
public class ExcelMapping {
    private static final Log log= LogFactory.getLog(ExcelMapping.class);
    Map<String,List<Object>> map=new HashMap<>();
    private  ExcelUtil excelUtil;
    private  Class aClass;
    public ExcelMapping() throws Exception{

    }
    public ExcelMapping(File file, Class aClass) throws Exception{
        this.aClass=aClass;
        this.excelUtil=new ExcelUtil();
        excelUtil.loadWorkBook(file);
    }
    public ExcelMapping(InputStream inputStream, Class aClass) throws Exception{
        this.aClass=aClass;
        this.excelUtil=new ExcelUtil();
        excelUtil.loadWorkBook(inputStream);
    }
    public List<Object> excelToList(String sheetName) throws Exception{
        return readExcelBean(excelUtil.getWb().getSheet(sheetName));
    }
    public List<Object> excelToList(ExcelUtil excelUtil,String sheetName,Class aClass) throws  Exception{
        this.aClass=aClass;
        this.excelUtil=excelUtil;
        return readExcelBean(excelUtil.getWb().getSheet(sheetName));
    }
    private  List<Object> readExcelBean(Sheet sheet) throws Exception{
        Row firstRow=sheet.getRow(0);
        Map<String, Integer> fieldMap = new HashMap<>();

        for (String fieldName:getFields()){
            for(int i=0;i<firstRow.getLastCellNum();i++){
                if (firstRow.getCell(i).toString().replace("_","").toLowerCase().equals(fieldName)){
                    fieldMap.put(fieldName,i);
                    break;
                }
            }
        }
        List<Object> rowList=new ArrayList<>();
        for (int i=1;i<=sheet.getLastRowNum();i++){
            Row row = sheet.getRow(i);
            if (row==null){
                break;
            }
            log.info("获取sheet表第<"+i+">行数据");
            rowList.add(getObj(fieldMap,row));
        }
        return rowList;
    }
    private  Object getObj(Map<String,Integer>fieldMap, Row row) throws Exception{
        Object sm= aClass.newInstance();
        Field[] fields=aClass.getDeclaredFields();
        for (Field field:fields){
            String fileName=field.getName().toLowerCase();
            if(!fieldMap.containsKey(fileName))continue;
            field.setAccessible(true);
            int colNum=fieldMap.get(fileName);
            Object p = excelUtil.getCellValue(row, colNum);
            log.info("-------------"+field.getName()+"属性赋值---------------");
            log.info("######类属性类型："+field.getType().getName()+"---#######excel的cell值类型："+p.getClass().getTypeName());
            p=TransType.translate(p.getClass(),field.getType(),p);
            field.set(sm,p);
        }
        if(aClass.getSuperclass()!=null){
            Field[] fieldss=aClass.getSuperclass().getDeclaredFields();
            for (Field field:fieldss){
                String filedName=field.getName().toLowerCase();
                if(!fieldMap.containsKey(filedName))continue;
                field.setAccessible(true);
                int colNum=fieldMap.get(filedName);
                Object p = excelUtil.getCellValue(row, colNum);
                log.info("-------------"+field.getName()+"属性赋值---------------");
                log.info("######类属性类型："+field.getType().getName()+"---#######excel的cell值类型："+p.getClass().getTypeName());
                p=TransType.translate(p.getClass(),field.getType(),p);
                field.set(sm,p);
            }
        }
        return sm;
    }

    private   List<String> getFields(){
        List<String> list=new ArrayList<>();
        for (Field field:aClass.getDeclaredFields()){
            list.add(field.getName().toString().toLowerCase().replace("_",""));
        }
        if(aClass.getSuperclass()!=null){
            for (Field field:aClass.getSuperclass().getDeclaredFields()){
                list.add(field.getName().toString().toLowerCase().replace("_",""));
            }
        }
        System.out.println(list);
        return list;
    }
    public static void main(String[] args) throws Exception{
        String path="E:\\gt_eu_yearly_table1.xlsx";
        //ExcelMapping sectorHandler=new ExcelMapping(new File(path),GtEuYearlyTableWithBLOBs.class);
        //sectorHandler.excelToList("gt_eu_yearly_table");
    }
    /**
     * 在原有行插入一列数据
     * @param sheetName sheet名称
     * @param rowIndex 行索引
     * @param cellIndex 列索引
     * @param insertContent 插入内容
     */
    public void insertData(String sheetName,int rowIndex,int cellIndex,String insertContent){
        this.excelUtil.setCellValue(sheetName,rowIndex,cellIndex,insertContent);
    }
    /**
     * 获取输入流
     * @return
     */
    public InputStream getInputStrem(){
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            this.excelUtil.getWb().write(bos);
        } catch (Exception e) {
            e.printStackTrace();
        }

        ByteArrayInputStream bis = new ByteArrayInputStream(bos.toByteArray());
        return bis;
    }


    /**
     * 防止科学计数问题
     * 一些导入导入的值为数字，到后台会转换为科学计数
     * （防止导入的数据与用户输入的数据不一致）
     * @param sheetName
     * @return
     * @throws Exception
     */
    public List<Object> excelToListOfString(String sheetName) throws Exception{
        return readExcelBeanOfString(excelUtil.getWb().getSheet(sheetName));
    }

    /**
     * 一些导入导入的值为数字，到后台会转换为科学计数
     * （防止导入的数据与用户输入的数据不一致）
     * @param sheet
     * @return
     * @throws Exception
     */
    private  List<Object> readExcelBeanOfString(Sheet sheet) throws Exception{
        Row firstRow=sheet.getRow(0);
        Map<String, Integer> fieldMap = new HashMap<>();

        for (String fieldName:getFields()){
            for(int i=0;i<firstRow.getLastCellNum();i++){
                if (firstRow.getCell(i).toString().replace("_","").toLowerCase().equals(fieldName)){
                    fieldMap.put(fieldName,i);
                    break;
                }
            }
        }
        List<Object> rowList=new ArrayList<>();
        for (int i=1;i<=sheet.getLastRowNum();i++){
            Row row = sheet.getRow(i);
            if (row==null){
                break;
            }
            log.info("获取sheet表第<"+i+">行数据");
            rowList.add(getObjPreventScientificCounting(fieldMap,row));
        }
        return rowList;
    }

    /**
     * 一些导入导入的值为数字，到后台会转换为科学计数
     * （防止导入的数据与用户输入的数据不一致）
     * @param fieldMap
     * @param row
     * @return
     * @throws Exception
     */
    private  Object getObjPreventScientificCounting(Map<String,Integer>fieldMap, Row row) throws Exception{
        Object sm= aClass.newInstance();
        Field[] fields=aClass.getDeclaredFields();
        for (Field field:fields){
            String fileName=field.getName().toLowerCase();
            if(!fieldMap.containsKey(fileName))continue;
            field.setAccessible(true);
            int colNum=fieldMap.get(fileName);
            Object p = excelUtil.getCellValuePreventScientificCounting(row, colNum);
            log.info("-------------"+field.getName()+"属性赋值---------------");
            log.info("######类属性类型："+field.getType().getName()+"---#######excel的cell值类型："+p.getClass().getTypeName());
            p=TransType.translate(p.getClass(),field.getType(),p);
            field.set(sm,p);
        }
        if(aClass.getSuperclass()!=null){
            Field[] fieldss=aClass.getSuperclass().getDeclaredFields();
            for (Field field:fieldss){
                String filedName=field.getName().toLowerCase();
                if(!fieldMap.containsKey(filedName))continue;
                field.setAccessible(true);
                int colNum=fieldMap.get(filedName);
                Object p = excelUtil.getCellValuePreventScientificCounting(row, colNum);
                log.info("-------------"+field.getName()+"属性赋值---------------");
                log.info("######类属性类型："+field.getType().getName()+"---#######excel的cell值类型："+p.getClass().getTypeName());
                p=TransType.translate(p.getClass(),field.getType(),p);
                field.set(sm,p);
            }
        }
        return sm;
    }
}
